* This code reads and cleans the MidClose price data
clear

* Add Market Prices
global replication `c(pwd)'
global data "$replication\Data"
global inter "$replication\Dta - Intermediate"
global final "$replication\Dta - Final"
global graphs "$replication\Graphs"
import delimited "$data\MarketPrices.ToStata.Open.csv", varnames(1)

* Create Dates + Set as Time series
gen dates=date(v1,"MDY")
* format dates %td
* tsset dates
order dates, first
drop v1

* List Empty Variables - Those with Empty ISIN code, and drop'm after name capture in (r)
ds, has(type string) 
drop `r(varlist)'
* desc 
* This command drops ISIN for which there is no data
ds, has(type byte) 
drop `r(varlist)'
* desc 

* Dropping empty dates
drop if dates==.

* Rename, for consistency
rename *isin *

* If Testiting, uncomment to shrink the base...
* keep dates es0000012c46 es0000012e51 es0000012e69 es0000012e85 es0000012f43 es0202762003 es0302762127


* Need to drop observations with zeros
* Define as Panel Strcture
foreach x of var es* { 
	rename `x' oprice`x' 
} 
reshape long oprice, i(dates) j(isin) string
sort isin dates
order isin, first
encode isin, gen(sec_bnpid)
order sec_bnpid, first
xtset sec_bnpid dates
order dates, after(isin)
format dates %td
label var sec_bnpid "Security (BNP id)"

* Dropping Outlier 
drop if oprice<=50
label var oprice "price (opening)"

by sec_bnpid, sort: gen newsec_bnpind = _n == 1
gen sec_bnpid2 = sum(newsec_bnpind)
label var sec_bnpid2 "Sec Identifier"
order sec_bnpid2, after(sec_bnpid)
tab sec_bnpid2

**Drop Missing Observations to the Data Set is more manageable
drop if oprice==.

***** Plot of Prices that look like T-Bills (zero coupon)
* !saki: Price Figures not printing correctly...
xtline oprice if(sec_bnpid2>132&sec_bnpid2<=180), overlay
forvalues x=1/3 {
	xtline oprice if(sec_bnpid2>(`x'-1)*15&sec_bnpid2<=`x'*15), overlay
	* graph export "Price Figures\CloseMid\oPrice_group`x'.ps", as(ps) replace
	}

save "$inter\SpainMarketPricePanelOpen.dta", replace

